package com.wanda.util;

import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.List;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.wanda.model.ContractModel;

public class ReportExcel {
	public List<Hashtable<String, String>> orderPrint(List<ContractModel> contractModellist) throws Exception {
		List<ContractModel> orderPrintList = contractModellist;
		List<Hashtable<String, String>> list = new ArrayList<Hashtable<String, String>>();
		for (ContractModel orderPrint : orderPrintList) {
			System.out.println("orderPrint"+orderPrint);
			Hashtable<String, String> ht = new Hashtable<String, String>();
			Field[] fields = ContractModel.class.getDeclaredFields();
			for (int i = 0; i < fields.length; i++) {
				Field f = fields[i];
				String key = f.getName();
				f.setAccessible(true);
				String value = "";
				try {
					if (null != f.get(orderPrint))
						value = f.get(orderPrint).toString();
				} catch (Exception e) {
					e.printStackTrace();
					throw e;
				}
				ht.put(key, value);
			}
			list.add(ht);
		}
		return list;
	}
	public void getPrintFile(List<Hashtable<String,String>> list_info,OutputStream os){
		OPCPackage pkg = null;
		try{
			String file = null;
			file =this.getClass().getResource("/").getPath()+"excelTemplet/orderPrint.xls";
		
			pkg = OPCPackage.open(file);
			XSSFWorkbook hwb = new XSSFWorkbook(pkg);
			XSSFSheet sheet = hwb.getSheet("签约合同");
			Hashtable<String,String> hash_position = new Hashtable<String,String>();
			List<Short> list_hight = new ArrayList<Short>();
			int row_count = 15;
			int column_count = 10;
			for(int i=0;i<row_count;i++){
				XSSFRow row = sheet.getRow(i);
				if(row==null){
					list_hight.add((short)-1);
					continue;
				}else{
					list_hight.add(row.getHeight());
				}
				for(int j=0;j<column_count;j++){
					XSSFCell cell = row.getCell(j);
					if(cell==null)
						continue;
					int type = cell.getCellType();
					String value = null;
					if(type==XSSFCell.CELL_TYPE_NUMERIC){
						value = cell.getNumericCellValue()+"";
					}else{
						value = cell.getStringCellValue();
					}
					if(value!=null)hash_position.put(i+"_"+j, value);
				}
			}
			List<int[]> list_combine = getCombineCell(sheet);
			XSSFCellStyle style = (XSSFCellStyle) hwb.createCellStyle();
			style.setAlignment(XSSFCellStyle.ALIGN_LEFT);
			style.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);
			style.setWrapText(true);
			XSSFFont font = (XSSFFont) hwb.createFont();
			font.setFontHeightInPoints((short) 12);
			font.setFontName("宋体");
			font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
			style.setFont(font);
			for(int a=0;a<list_info.size();a++){
				Hashtable<String,String> order_info = list_info.get(a);
				for(int i=0;i<row_count;i++){
					XSSFRow row = sheet.getRow(i+a*row_count);
					if(row==null){
						row = sheet.createRow(i+a*row_count);
					}
					row.setHeight(list_hight.get(i));
					for(int j=0;j<column_count;j++){
						XSSFCell cell = row.getCell(j);
						if(cell ==null){
							cell = row.createCell(j);
						}
						cell.setCellStyle(style);
						cell.setCellType(XSSFCell.CELL_TYPE_STRING);
						String value = hash_position.get(i+"_"+j);
						if(value !=null){
							Enumeration<String> keys = order_info.keys();
							while(keys.hasMoreElements()){
								String key = keys.nextElement();
								String val = order_info.get(key);
								value = value.replace("${"+key.toLowerCase()+"}", val);
							}
							cell.setCellValue(value);
						}
					}
				}
				if(a>0){
					for(int i=0;i<list_combine.size();i++){
						int[] idxs = list_combine.get(i);
						CellRangeAddress cra =new CellRangeAddress(idxs[0]+a*row_count,idxs[1]+a*row_count,idxs[2],idxs[3]);
						sheet.addMergedRegion(cra);
					}
				}
			}
			hwb.write(os);
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				pkg.revert();
			}catch(Exception e){
				
			}
		}
		
	}
	private List<int[]> getCombineCell(XSSFSheet sheet) {
		int sheetmergerCount = sheet.getNumMergedRegions();
		List<int[]> list = new ArrayList<int[]>();
		for(int i = 0;i<sheetmergerCount;i++){
			CellRangeAddress ca = sheet.getMergedRegion(i);
			list.add(new int[]{ca.getFirstRow(),ca.getLastRow(),ca.getFirstColumn(),ca.getLastColumn()});
		}
		return list;
	}
}
